package com.bj58.ecdata.mailreport.dao;

import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.bj58.sfft.utility.dao.statementcreater.IStatementCreater;
import com.bj58.sfft.utility.dao.util.Common;
import com.bj58.sfft.utility.dao.util.JdbcUitl;
import com.bj58.sfft.utility.dao.util.OutSQL;

/**
 * 重写架构部的 DAOHandler 不替换关键字，没有防SQL注入功能
 * @author zhaoxiang 20150202
 *
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
class MyDAOHandler extends MyDAOBase
	 {
	   public MyDAOHandler(IStatementCreater creater)
	   {
	     this.psCreater = creater;
	   }
	 
	   public Object insert(Object bean) throws Exception
	   {
	   return insert(bean, this.insertUpdateTimeOut);
	   }
	 
	   public void upateEntity(Object bean) throws Exception
	   {
	   upateEntity(bean, this.qurryTimeOut);
	   }
	 
	   public <I> void upateByID(Class<?> clazz, String updateStatement, I id) throws Exception
	   {
	   upateByID(clazz, updateStatement, id, this.insertUpdateTimeOut);
	   }
	 
	   public void updateByCustom(Class<?> clazz, String updateStatement, String condition) throws Exception
	   {
	    updateByCustom(clazz, updateStatement, condition, this.insertUpdateTimeOut);
	   }
	 
	   public <I> void deleteByID(Class<?> clazz, I id) throws Exception
	   {
	     deleteByID(clazz, id, this.qurryTimeOut);
	   }
	 
	   public <I> void deleteByIDS(Class<?> clazz, I[] ids) throws Exception
	   {
	   deleteByIDS(clazz, ids, this.qurryTimeOut);
	   }
	 
	   public void deleteByCustom(Class<?> clazz, String condition) throws Exception
	   {
	   deleteByCustom(clazz, condition, this.qurryTimeOut);
	   }
	 
	   public <I> Object get(Class<?> clazz, I id) throws Exception
	   {
	    return get(clazz, id, this.qurryTimeOut);
	   }
	 
	   public List<?> getListByCustom(Class<?> clazz, String columns, String condition, String orderBy) throws Exception
	   {
	    return getListByCustom(clazz, columns, condition, orderBy, this.qurryTimeOut);
	   }
	 
	   public List<?> getListByPage(Class<?> clazz, String condition, String columns, int page, int pageSize, String orderBy) throws Exception
	   {
	    return getListByPage(clazz, condition, columns, page, pageSize, orderBy, this.qurryTimeOut);
	   }
	 
	   public List<Object[]> customSql(String sql, int columnCount) throws Exception
	   {
	     return customSql(sql, columnCount, this.qurryTimeOut);
	   }
	 
	   public void customSqlNoReturn(String sql) throws Exception
	   {
			customSqlNoReturn(sql, this.qurryTimeOut);
	   }
	 
	   public int getCount(Class<?> clazz, String condition) throws Exception
	   {
			return getCount(clazz, condition, this.qurryTimeOut);
	   }
	 
	   public <T, I> List<T> getListByIDS(Class<T> clazz, I[] ids) throws Exception
	   {
			return getListByIDS(clazz, ids, this.qurryTimeOut);
	   }
	 
	   public void updateByCustom(Class<?> clazz, Map<String, Object> kv, Map<String, Object> condition) throws Exception
	   {
			updateByCustom(clazz, kv, condition, this.insertUpdateTimeOut);
	   }
	 
	   public void deleteByCustom(Class<?> clazz, Map<String, Object> condition) throws Exception
	   {
			deleteByCustom(clazz, condition, this.qurryTimeOut);
	   }
	 
	   public <T> List<T> getListByCustom(Class<T> clazz, String columns, Map<String, Object> condition, String orderBy) throws Exception
	   {
			return getListByCustom(clazz, columns, condition, orderBy, this.qurryTimeOut);
	   }
	 
	   public <T> List<T> getListByPage(Class<T> clazz, Map<String, Object> condition, String columns, int page, int pageSize, String orderBy) throws Exception
	   {
			return getListByPage(clazz, condition, columns, page, pageSize, orderBy, this.qurryTimeOut);
	   }
	 
	   public int getCount(Class<?> clazz, Map<String, Object> condition) throws Exception
	   {
		return getCount(clazz, condition, this.qurryTimeOut);
	   }
	 
	   public <T> List<T> getListBySQL(Class<T> clazz, String sql, Object... param)
	     throws Exception
	   {
	    return getListBySQL(clazz, sql, this.qurryTimeOut, param);
	   }
	 
	   public int execBySQL(String sql, Object... param) throws Exception
	   {
	    return execBySQL(sql, this.insertUpdateTimeOut, param);
	   }
	 
	   public int getCountBySQL(String sql, Object... param) throws Exception
	   {
	    return getCountBySQL(sql, this.qurryTimeOut, param);
	   }
	 
	   public int getCountBySQL(String sql, int timeOut, Object... param)
	     throws Exception
	   {
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	     try {
	      conn = this.connHelper.getReadConnection();
	      ps = conn.prepareStatement(sql);
	      ps.setQueryTimeout(timeOut);
	 
	      if (param != null) {
	        for (int i = 0; i < param.length; ++i) {
	          Common.setPara(ps, param[i], i + 1);
	         }
	       }
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql, startTime);
	      if (rs.next()) {
	        int i = rs.getInt(1);
	         return i;
	       }
	      return 0;
	     } catch (Exception ex) {
		      logger.error("getListByCustom error sql:" + sql, ex);
		      throw ex;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public <T> List<T> getListBySQL(Class<T> clazz, String sql, int timeOut, Object... param) throws Exception
	   {
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    List<T> dataList = null;
	     try {
	      conn = this.connHelper.getReadConnection();
	      ps = conn.prepareStatement(sql);
	      ps.setQueryTimeout(timeOut);
	 
	      if (param != null) {
	        for (int i = 0; i < param.length; ++i) {
	          Common.setPara(ps, param[i], i + 1);
	         }
	       }
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql, startTime);
	      dataList = populateData(rs, clazz);
	     } catch (SQLException e) {
	      logger.error("getListByCustom error sql:" + sql, e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	    return dataList;
	   }
	 
	   public int execBySQL(String sql, int timeOut, Object... param) throws Exception
	   {
	    Connection conn = null;
	    PreparedStatement ps = null;
	     try
	     {
	      conn = this.connHelper.get();
	      ps = conn.prepareStatement(sql);
	      ps.setQueryTimeout(timeOut);
	 
	      if (param != null) {
	        for (int i = 0; i < param.length; ++i) {
	          Common.setPara(ps, param[i], i + 1);
	         }
	       }
	      long startTime = System.currentTimeMillis();
	      int result = ps.executeUpdate();
	      printlnSqlAndTime(sql, startTime);
	      return result;
	     }
	     catch (SQLException e)
	     {
	    	 throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public void updateByCustom(Class<?> clazz, Map<String, Object> kv, Map<String, Object> condition, int timeOut)
	     throws Exception
	   {
	    throw new UnsupportedOperationException("Not supported");
	   }
	 
	   public void deleteByCustom(Class<?> clazz, Map<String, Object> condition, int timeOut) throws Exception
	   {
	    throw new UnsupportedOperationException("Not supported");
	   }
	 
	   public <T> List<T> getListByCustom(Class<T> clazz, String columns, Map<String, Object> condition, String orderBy, int timeOut)
	     throws Exception
	   {
	    throw new UnsupportedOperationException("Not supported");
	   }
	 
	   public <T> List<T> getListByPage(Class<T> clazz, Map<String, Object> condition, String columns, int page, int pageSize, String orderBy, int timeOut)
	     throws Exception
	   {
	    throw new UnsupportedOperationException("Not supported");
	   }
	 
	   public int getCount(Class<?> clazz, Map<String, Object> condition, int timeOut)
	     throws Exception
	   {
	    throw new UnsupportedOperationException("Not supported");
	   }
	 
	   public <T, I> List<T> getListByIDS(Class<T> clazz, I[] ids, int timeOut)
	     throws Exception
	   {
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    List<T> dataList = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.getReadConnection();
	      ps = this.psCreater.createGetByIDS(clazz, conn, ids, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	      dataList = populateData(rs, clazz);
	     } catch (SQLException e) {
	      logger.error("getListByCustom error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	    return dataList;
	   }
	 

	   public Object insert(Object bean, int timeOut)
	       throws Exception
	   {
	       Class beanCls;
	       Connection conn;
	       ResultSet rs;
	       PreparedStatement ps;
	       Object rst;
	       OutSQL sql;
	       beanCls = bean.getClass();
	       conn = null;
	       rs = null;
	       ps = null;
	       rst = null;
	       sql = new OutSQL();
	       try
	       {
	           conn = connHelper.get();
	           ps = psCreater.createInsert(bean, conn, sql);
	           ps.setQueryTimeout(timeOut);
	           long startTime = System.currentTimeMillis();
	           ps.executeUpdate();
	           printlnSqlAndTime(sql.getRealSql(), startTime);
	           boolean isProc = false;
	           Class clsAry[] = ps.getClass().getInterfaces();
	           Class aclass[];
	           int j = (aclass = clsAry).length;
	           for(int i = 0; i < j; i++)
	           {
	               Class cls = aclass[i];
	               if(cls != java.sql.CallableStatement.class)
	                   continue;
	               isProc = true;
	               break;
	           }

	           List identityFields = Common.getIdentityFields(beanCls);
	           if(isProc)
	           {
	               if(identityFields.size() == 1)
	                   rst = ((CallableStatement)ps).getObject(Common.getDBCloumnName(beanCls, (Field)identityFields.get(0)));
	           } else
	           if(identityFields.size() == 1)
	           {
	               rs = ps.getGeneratedKeys();
	               if(rs.next())
	               {
	                   List idFieldList = Common.getIdFields(beanCls);
	                   if(idFieldList.size() == 1)
	                   {
	                       if(((Field)idFieldList.get(0)).getType() == Integer.TYPE || ((Field)idFieldList.get(0)).getType() == java.lang.Integer.class)
	                           rst = Integer.valueOf(rs.getInt(1));
	                       else
	                       if(((Field)idFieldList.get(0)).getType() == Long.TYPE || ((Field)idFieldList.get(0)).getType() == java.lang.Long.class)
	                           rst = Long.valueOf(rs.getLong(1));
	                       else
	                       if(((Field)idFieldList.get(0)).getType() == java.lang.String.class)
	                           rst = rs.getString(1);
	                       else
	                           rst = rs.getObject(1);
	                   } else
	                   {
	                       rst = rs.getObject(1);
	                   }
	               }
	           } else
	           if(identityFields.size() == 0)
	           {
	               List idFields = Common.getIdFields(beanCls);
	               if(idFields.size() == 1)
	               {
	                   Field id = (Field)idFields.get(0);
	                   id.setAccessible(true);
	                   rst = id.get(bean);
	               }
	           }
	       }
	       catch(Exception e)
	       {
	           logger.error((new StringBuilder("insert error sql:")).append(sql.getSql()).toString(), e);
	           throw e;
	       }
	     finally{
		       JdbcUitl.closeResultSet(rs);
		       JdbcUitl.closeStatement(ps);
		       connHelper.release(conn);
	     }
	       return rst;
	   }

	   
	   
	   public void upateEntity(Object bean, int timeOut) throws Exception {
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createUpdateEntity(bean, conn, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.executeUpdate();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("update error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public <I> void upateByID(Class<?> clazz, String updateStatement, I id, int timeOut) throws Exception {
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createUpdateByID(clazz, conn, updateStatement, id, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.executeUpdate();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("update error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public void updateByCustom(Class<?> clazz, String updateStatement, String condition, int timeOut) throws Exception {
//	    condition = SqlInjectHelper.simpleFilterSql(condition);
//	    updateStatement = SqlInjectHelper.simpleFilterSql(updateStatement);
	 
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createUpdateByCustom(clazz, conn, updateStatement, condition, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.executeUpdate();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("update error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public <I> void deleteByID(Class<?> clazz, I id, int timeOut) throws Exception {
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createDelete(clazz, conn, id, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.execute();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("delete error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public <I> void deleteByIDS(Class<?> clazz, I[] ids, int timeOut) throws Exception {
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createDeleteByIDS(clazz, conn, ids, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.execute();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("delete error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public void deleteByCustom(Class<?> clazz, String condition, int timeOut) throws Exception {
//	    condition = SqlInjectHelper.simpleFilterSql(condition);
	 
	    Connection conn = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try {
	      conn = this.connHelper.get();
	      ps = this.psCreater.createDeleteByCustom(clazz, conn, condition, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      ps.execute();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	     } catch (Exception e) {
	      logger.error("delete error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	   }
	 
	   public <I> Object get(Class<?> clazz, I id, int timeOut) throws Exception {
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    List<?> dataList = null;
	    OutSQL sql = new OutSQL();
	     try
	     {
	      conn = this.connHelper.getReadConnection();
	 
	      ps = this.psCreater.createGetEntity(clazz, conn, id, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	      dataList = populateData(rs, clazz);
	     } catch (Exception e) {
	      logger.error("get error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	 
	    if ((dataList != null) && (dataList.size() > 0)) {
	      return dataList.get(0);
	     }
	    return null;
	   }
	 
	   public List<?> getListByCustom(Class<?> clazz, String columns, String condition, String orderBy, int timeOut) throws Exception
	   {
//	    columns = SqlInjectHelper.simpleFilterSql(columns);
//	    condition = SqlInjectHelper.simpleFilterSql(condition);
//	    orderBy = SqlInjectHelper.simpleFilterSql(orderBy);
	 
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    List<?> dataList = null;
	    OutSQL sql = new OutSQL();
	     try
	     {
	      conn = this.connHelper.getReadConnection();
	 
	      ps = this.psCreater.createGetByCustom(clazz, conn, columns, condition, orderBy, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	      dataList = populateData(rs, clazz);
	     } catch (SQLException e) {
	      logger.error("getListByCustom error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	    return dataList;
	   }
	 
	   public List<?> getListByPage(Class<?> clazz, String condition, String columns, int page, int pageSize, String orderBy, int timeOut)
	     throws Exception
	   {
//	    columns = SqlInjectHelper.simpleFilterSql(columns);
//	    condition = SqlInjectHelper.simpleFilterSql(condition);
//	    orderBy = SqlInjectHelper.simpleFilterSql(orderBy);
	 
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    List<?> dataList = null;
	    OutSQL sql = new OutSQL();
	     try
	     {
	      conn = this.connHelper.getReadConnection();
	 
	      ps = this.psCreater.createGetByPage(clazz, conn, condition, columns, page, pageSize, orderBy, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	      dataList = populateData(rs, clazz);
	     } catch (Exception e) {
	      logger.error("getListByPage error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	    return dataList;
	   }
	 
	   public int getCount(Class<?> clazz, String condition, int timeOut)
	     throws Exception
	   {
//	    condition = SqlInjectHelper.simpleFilterSql(condition);
	 
	    int count = 0;
	    Connection conn = null;
	    ResultSet rs = null;
	    PreparedStatement ps = null;
	    OutSQL sql = new OutSQL();
	     try
	     {
	      conn = this.connHelper.getReadConnection();
	 
	      ps = this.psCreater.createGetCount(clazz, conn, condition, sql);
	      ps.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = ps.executeQuery();
	      printlnSqlAndTime(sql.getRealSql(), startTime);
	         if(rs.next())
	             count = rs.getInt(1);
	     }
	     catch (Exception e) {
	      logger.error("getCount error sql:" + sql.getSql(), e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(ps);
	      this.connHelper.release(conn);
	     }
	    return count;
	   }
	 
	   public List<Object[]> customSql(String sql, int columnCount, int timeOut)
	     throws Exception
	   {
	    List list = new ArrayList();
	    Connection conn = null;
	    ResultSet rs = null;
	    Statement stmt = null;
	     try {
	      conn = this.connHelper.get();
	      stmt = conn.createStatement();
	      stmt.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      rs = stmt.executeQuery(sql);
	      printlnSqlAndTime(sql, startTime);
	      while (rs.next()) {
	        Object[] objAry = new Object[columnCount];
	        for (int i = 0; i < columnCount; ++i) {
	          objAry[i] = rs.getObject(i + 1);
	         }
	        list.add(objAry);
	       }
	     } catch (Exception e) {
	      logger.error("sql:" + sql, e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeResultSet(rs);
	      JdbcUitl.closeStatement(stmt);
	      this.connHelper.release(conn);
	     }
	    return list;
	   }
	 
	   public void customSqlNoReturn(String sql, int timeOut) throws Exception {
	    Connection conn = null;
	    Statement stmt = null;
	     try {
	      conn = this.connHelper.get();
	      stmt = conn.createStatement();
	      stmt.setQueryTimeout(timeOut);
	      long startTime = System.currentTimeMillis();
	      stmt.execute(sql);
	      printlnSqlAndTime(sql, startTime);
	     } catch (Exception e) {
	      logger.error("sql:" + sql, e);
	      throw e;
	     }
	     finally {
	      JdbcUitl.closeStatement(stmt);
	      this.connHelper.release(conn);
	     }
	   }
	   
	}
